﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using IHISCETeim.Library;
using IHISCETeim.Model;


namespace IHISCETeim.Web.Export.FXModel
{
    public partial class FXFinanceLease : ExportPageBase
    {
      
       protected override string xlsTemplateFileName
        {
            get
            {
                switch (LeaseStatus)
                { 
                    case 4:
                        return "融资租赁进度表(已放款).xls";
                        break;
                    case 3:
                        return "融资租赁进度表(待放款).xls";
                        break;
                    case 2:
                        return "融资租赁进度表(审核通过).xls";
                        break;
                    case 1:
                        return "融资租赁进度表(租赁公司审核中).xls";
                        break;
                    case 0:
                        return "融资租赁进度表(IHISCE审核中).xls";
                        break;
                    case -1:
                        return "融资租赁进度表(全部).xls";
                        break;
                    case -2:
                        return "融资租赁进度表(未上报).xls";
                        break;
                }
                return "融资租赁进度表(已放款).xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "Sheet1";
            }
        }

        protected int UserId
        {
            get
            {
                return GetRequestValue<int>("u", 0);
            }
        }
        #region 属性 (查询条件)

        protected int onlyshowExpiryPeriods
        {
            get
            {
                return GetRequestValue<int>("onlyshowExpiryPeriods", 0);
            }
        }
        protected string AliasName
        {
            get
            {
                return GetRequestValue<string>("AliasName", "");

            }
        }
        protected string CustomerName
        {
            get
            {
                return GetRequestValue<string>("CustomerName", "");

            }
        }
        protected string LeaseCompany
        {
            get
            {
                return GetRequestValue<string>("LeaseCompany", "");
            }
        }

        protected string SerialNumber
        {
            get
            {
                return GetRequestValue<string>("SerialNumber", "");
            }
        }
        protected string ContractNum
        {
            get
            {
                return GetRequestValue<string>("ContractNum", "");

            }
        }
        protected string FnncLsId
        {
            get
            {
                return GetRequestValue<string>("FnncLsId", "");

            }
        }

        protected string LoansDateFrom
        {
            get
            {
                return GetRequestValue<string>("LoansDateFrom", "");
            }
        }

        protected string LoansDateTo
        {
            get
            {
                return GetRequestValue<string>("LoansDateTo", LoansDateFrom);
            }
        }

        protected int LeaseStatus
        {
            get
            {
                return GetRequestValue<int>("LeaseStatus", -1);

            }
        }



        protected override string orderField
        {
            get
            {
                return string.IsNullOrEmpty(base.orderField) ? "AliasName" : base.orderField;
            }
        }
        protected override string orderDirection
        {
            get
            {
                return string.IsNullOrEmpty(base.orderDirection) ? "ASC" : base.orderDirection;
            }
        }
        #endregion       

        #region 获取相关文本说明

        #region 状态跳转

        protected string GetGoLeaseStatusUrl(object _LeaseStatus)
        {
            return GetLeaseStatusText(_LeaseStatus);
            
            // return string.Format(goUrl, colorHtml, LeaseStatusText, NextLeaseStatusText, _FnncLsId, LeaseStatus >= 4 ? 0 : LeaseStatus+1);



        }
        /// <summary>
        /// 获取进度状态文本
        /// </summary>
        /// <param name="_LeaseStatus"></param>
        /// <returns></returns>
        protected string GetLeaseStatusText(object _LeaseStatus)
        {
            int LeaseStatus = Convert.ToInt16(_LeaseStatus);
            string[] _LeaseStatusArr = new string[] { "IHISCE审核中", "租赁公司审核中", "通过审核", "待放款", "已放款" };
            if (LeaseStatus > 4)
                return "";
            return _LeaseStatusArr[LeaseStatus].ToString();
        }
        #endregion
        /// <summary>
        /// 获取各状态的总数
        /// </summary>
        /// <param name="lease">状态名称 : 审核中 ,审核通过,待放款,已放款</param>
        /// <returns></returns>
        protected string GetLeaseStatusCount(string lease)
        {
            try
            {
                DataRow dr = leaseStatusDT.Rows[0];
                return dr[lease].ToString();
            }
            catch
            {
                return "-";
            }
        }

        private DataTable _leaseStatusDT = null;
        private DataTable leaseStatusDT
        {
            get
            {
                if (_leaseStatusDT == null)
                {
                    string agentsqltemp = SAreaManagerAgentController.GetMyAgentSqlWhere();
                    if (string.IsNullOrEmpty(agentsqltemp))
                        agentsqltemp = "'none'";
                    _leaseStatusDT = FXFinanceLeaseController.GetLeaseStatusCount(agentsqltemp);
                }
                return _leaseStatusDT;
            }
        }


        protected string GetDateShowType(object datevalue)
        {
            if (datevalue == DBNull.Value)
                return "";
            DateTime dtemp = Convert.ToDateTime(datevalue);
            return dtemp.ToString("yyyy-MM-dd");
        }

        
        protected string getCleanUpStatus(object _CleanUpStatus)
        {
            short CleanUpStatus = Convert.ToInt16(_CleanUpStatus);
            return CleanUpStatus == -1 ? "未清" : CleanUpStatus == 0 ? "已到期未结清" : "已结清";
        }
        /// <summary>
        /// 投保情况 1已投保,0未投保
        /// </summary>
        /// <param name="_OInsureStatus"></param>
        /// <returns></returns>
        protected string getOInsureStatus(object _OInsureStatus)
        {
            short OInsureStatus = Convert.ToInt16(_OInsureStatus);
            return OInsureStatus == 0 ? "未投保" : "已投保";
        }
        /// <summary>
        /// 1停止服务(已到期的情况) .0正常
        /// </summary>
        /// <param name="_GPSStatus"></param>
        /// <returns></returns>
        protected string getGPSStatus(object _GPSStatus)
        {
            short GPSStatus = Convert.ToInt16(_GPSStatus);
            return GPSStatus == 1 ? "停止服务" : "服务中";
        }
        /// <summary>
        /// 资料、协议收集是否完成
        /// 0未完成，1已完成,2只承诺函，3缺承诺函
        /// </summary>
        /// <param name="_MaterialStatus"></param>
        /// <returns></returns>
        protected string getMaterialStatus(object _MaterialStatus)
        {
            short MaterialStatus = Convert.ToInt16(_MaterialStatus);
            switch (MaterialStatus)
            {
                case 0:
                    return "未完成";
                    break;
                case 1:
                    return "已完成";
                    break;
                case 2:
                    return "只承诺函";
                    break;
                case 3:
                    return "缺承诺函";
                    break;

            }
            return "";
        }
        /// <summary>
        /// 管理费支付状态 
        /// 1已付,0未付
        /// </summary>
        /// <param name="_LMFeeStatus"></param>
        /// <returns></returns>
        protected string getLMFeeStatus(object _LMFeeStatus)
        {
            short LMFeeStatus = Convert.ToInt16(_LMFeeStatus);
            return LMFeeStatus == 0 ? "未付" : "已付";
        }

        /// <summary>
        /// 应付首付款支付状态 
        /// 1已付,0未付,2无需支付(小于等0）
        /// </summary>
        /// <param name="_LMFeeStatus"></param>
        /// <returns></returns>
        protected string getLPayStatus(object _LPayStatus)
        {
            short LPayStatus = Convert.ToInt16(_LPayStatus);
            return LPayStatus == 0 ? "未付" : LPayStatus == 1 ? "已付" : "无需支付";
        }

        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            DataBind();
        }
        protected string GetWhereSql()
        {
            string wheresql = "";
            if (UserId > 0)
            {
                string agentsqltemp = SAreaManagerAgentController.GetMyAgentSqlWhere(UserId);
                if (string.IsNullOrEmpty(agentsqltemp))
                    return "1<>1";
                wheresql = string.Format(" AliasName in ({0})", agentsqltemp);
            }

            if (LeaseStatus > -1)
            {
                string LeaseStatussql = QueryOperatorsEnumHelper.ConvertCommonOperator("LeaseStatus", QueryOperatorsEnum.等于, LeaseStatus.ToString());
                if (string.IsNullOrEmpty(wheresql))
                    wheresql = LeaseStatussql;
                else
                    wheresql += " and " + LeaseStatussql;
            }
            else
            {
                string LeaseStatussql = string.Format("LeaseStatus in ({0})", "0,1,2,3,4");
                if (string.IsNullOrEmpty(wheresql))
                    wheresql = LeaseStatussql;
                else
                    wheresql += " and " + LeaseStatussql;
            }

            if (!AliasName.IsEmptyOrNull())
            {
                string aliasNamesql = QueryOperatorsEnumHelper.ConvertCommonOperator("AliasName", QueryOperatorsEnum.等于, AliasName);
                if (string.IsNullOrEmpty(wheresql))
                    wheresql = aliasNamesql;
                else
                    wheresql += " and " + aliasNamesql;
            }

            if (!LeaseCompany.IsEmptyOrNull())
            {
                string aliasNamesql = QueryOperatorsEnumHelper.ConvertCommonOperator("LeaseCompany", QueryOperatorsEnum.等于, LeaseCompany);
                if (string.IsNullOrEmpty(wheresql))
                    wheresql = aliasNamesql;
                else
                    wheresql += " and " + aliasNamesql;
            }

            if (!CustomerName.IsEmptyOrNull())
            {
                string CustomerNamesql = QueryOperatorsEnumHelper.ConvertCommonOperator("CustomerName", QueryOperatorsEnum.包含, CustomerName);
                if (string.IsNullOrEmpty(wheresql))
                    wheresql = CustomerNamesql;
                else
                    wheresql += " and " + CustomerNamesql;
            }


            if (!SerialNumber.IsEmptyOrNull())
            {
                string SerialNumbersql = QueryOperatorsEnumHelper.ConvertCommonOperator("SerialNumber", QueryOperatorsEnum.包含, SerialNumber);
                if (string.IsNullOrEmpty(wheresql))
                    wheresql = SerialNumbersql;
                else
                    wheresql += " and " + SerialNumbersql;
            }

            if (!ContractNum.IsEmptyOrNull())
            {
                string ContractNumsql = QueryOperatorsEnumHelper.ConvertCommonOperator("ContractNum", QueryOperatorsEnum.包含, ContractNum);
                if (string.IsNullOrEmpty(wheresql))
                    wheresql = ContractNumsql;
                else
                    wheresql += " and " + ContractNumsql;
            }

            if (!FnncLsId.IsEmptyOrNull())
            {
                string FnncLsIdsql = QueryOperatorsEnumHelper.ConvertCommonOperator("FnncLsId", QueryOperatorsEnum.等于, FnncLsId);
                if (string.IsNullOrEmpty(wheresql))
                    wheresql = FnncLsIdsql;
                else
                    wheresql += " and " + FnncLsIdsql;
            }

            if (onlyshowExpiryPeriods > 0)
            {
                string FnncLsIdsql = onlyshowExpiryPeriods == 30 ? QueryOperatorsEnumHelper.ConvertCommonOperator("LExpiryPeriods", QueryOperatorsEnum.大于等于, "3") : QueryOperatorsEnumHelper.ConvertCommonOperator("LExpiryPeriods", QueryOperatorsEnum.等于, onlyshowExpiryPeriods.ToString());
                if (string.IsNullOrEmpty(wheresql))
                    wheresql = FnncLsIdsql;
                else
                    wheresql += " and " + FnncLsIdsql;
            }

            if (!string.IsNullOrEmpty(LoansDateFrom))
            {
                string LoansDateFromsql = QueryOperatorsEnumHelper.ConvertCommonOperator("LoansDate", QueryOperatorsEnum.范围, LoansDateFrom + " 00:00:00|" + LoansDateTo + " 23:59:59");
                if (string.IsNullOrEmpty(wheresql))
                    wheresql = LoansDateFromsql;
                else
                    wheresql += " and " + LoansDateFromsql;
            }

            return wheresql;
        }
        protected DataTable showdt = null;
        private void DataBind()
        {
            int pageNum = 1;
            int numPerPage = 99999;
            int TotalCount = 0;
            DataTableCollection dtc = null;
            //  sumdt = dtc[1];
            switch (LeaseStatus)
            {

                case -1:
                    dtc = FXFinanceLeaseController.GetPagination(GetWhereSql(), pageNum, numPerPage, SQLSort, ref TotalCount);
                    export_1(dtc[0]);                   
                    break;
                case 0:
                    dtc = FXFinanceLeaseController.GetPagination(GetWhereSql(), pageNum, numPerPage, SQLSort, ref TotalCount);
                   // showdt = dtc[0];
                    export0(dtc[0]);  
                    break;
                case 1:
                    dtc = FXFinanceLeaseController.GetPagination(GetWhereSql(), pageNum, numPerPage, SQLSort, ref TotalCount);
                    showdt = dtc[0];
                    export1(dtc[0]);  
                    break;
                case 2:
                    dtc = FXFinanceLeaseController.GetPagination(GetWhereSql(), pageNum, numPerPage, SQLSort, ref TotalCount);
                    //showdt = dtc[0];
                    export2(dtc[0]);  
                    break;
                case 3:
                    dtc = FXFinanceLeaseController.GetPagination(GetWhereSql(), pageNum, numPerPage, SQLSort, ref TotalCount);
                    export3(dtc[0]);                   
                    break;
                case 4:
                    dtc = FXFinanceLeaseController.GetPagination(GetWhereSql(), pageNum, numPerPage, SQLSort, ref TotalCount);
                   // showdt = dtc[0];
                    export4(dtc[0]);
                    break;
            }

        }

        /// <summary>
        ///全部
        /// </summary>
        /// <param name="exportDT"></param>
        private void export_1(DataTable exportDT)
        {
            //DataTable exportDT = dts[0];

            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //写标题
            HSSFRow rowTitle = (HSSFRow)sheet1.CreateRow(0);
            string DTTitle = "融资租赁进度表(全部)";

            ICell cell = rowTitle.CreateCell(0);
            cell.SetCellValue(DTTitle);
            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeight = 20 * 20;
            style.SetFont(font);
            cell.CellStyle = style;

            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 29));

            //FOR写入
            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {


                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(GetGoLeaseStatusUrl(dr["LeaseStatus"].ToString()));//编号
                row1.CreateCell(1).SetCellValue(dr["FnncLsId"].ToString());//编号
                // row1.CreateCell(1).SetCellValue(dr["ContractNum"].ToString());//合同号
                row1.CreateCell(2).SetCellValue(dr["LeaseCompany"].ToString());//租赁公司
                row1.CreateCell(3).SetCellValue(dr["AliasName"].ToString());//经销商
                row1.CreateCell(4).SetCellValue(dr["CustomerName"].ToString());//用户
                row1.CreateCell(5).SetCellValue(dr["MachineType"].ToString());//机型
                row1.CreateCell(6).SetCellValue(dr["SerialNumber"].ToString());//机号
                //row1.CreateCell(7).SetCellValue(dr["LTotalPeriods"].ToString());//总期数
                //row1.CreateCell(8).SetCellValue(dr["LEvadePeriods"].ToString());//应付期数
                //row1.CreateCell(9).SetCellValue(dr["LPaidPeriods"].ToString());//已付期数
                //row1.CreateCell(10).SetCellValue(dr["LExpiryPeriods"].ToString());//逾期期数
                //row1.CreateCell(11).SetCellValue(dr["LastPayDate"].ToString());//最后支付时间
                row1.CreateCell(7).SetCellValue(dr["LExpiresYear"].ToString());//期限(年)
                row1.CreateCell(8).SetCellValue(GetDBDouble(dr["LTotalPrice"]));//售价
                row1.CreateCell(9).SetCellValue(dr["LowProportion"].ToString());//首付(%)
                row1.CreateCell(10).SetCellValue(GetDBDouble(dr["LDownAmount"]));//首付金额
                row1.CreateCell(11).SetCellValue(GetDBDouble(dr["LAmountFinancing"]));//融资额
                string LMonthRentStr = "";
                LMonthRentStr = dr["LMonthRent"].ToString();
                LMonthRentStr += Convert.ToDecimal(dr["LMonthRent2"]) == 0 ? "" : ("/" + dr["LMonthRent2"].ToString());
                row1.CreateCell(12).SetCellValue(LMonthRentStr);//月租金
                string LExpiresMonthStr = "";
                LExpiresMonthStr = dr["LExpiresMonth"].ToString();
                LExpiresMonthStr += Convert.ToDecimal(dr["LExpiresMonth2"]) == 0 ? "" : ("/" + dr["LExpiresMonth2"].ToString());
                row1.CreateCell(13).SetCellValue(LExpiresMonthStr);//期数
                //row1.CreateCell(19).SetCellValue(GetDBDouble(dr["LEvadeRent"]));//总应付租金
                //row1.CreateCell(20).SetCellValue(GetDBDouble(dr["LPaidRent"]));//累计已付租金
                //row1.CreateCell(21).SetCellValue(GetDBDouble(dr["LResiduerRent"]));//剩余租金
                row1.CreateCell(14).SetCellValue(GetDBDouble(dr["LContractAmount"]));//合同金额
                row1.CreateCell(15).SetCellValue(GetDBDouble(dr["LByStages"]));//首付(置换)分期申请
                row1.CreateCell(16).SetCellValue(GetDBDouble(dr["LPay"]));//应付首付款
                row1.CreateCell(17).SetCellValue(getLPayStatus(dr["LPayStatus"]));//应付首付款支付
                row1.CreateCell(18).SetCellValue(GetDBDouble(dr["LMFee"]));//管理费
                row1.CreateCell(19).SetCellValue(getLMFeeStatus(dr["LMFeeStatus"]));//管理费支付
                //row1.CreateCell(28).SetCellValue(getMaterialStatus(dr["MaterialStatus"]));//资料、协议收集完
                row1.CreateCell(20).SetCellValue(GetDateShowType(dr["LOrderDate"]));//接单日期
                row1.CreateCell(21).SetCellValue(GetDateShowType(dr["OGetDate"]));//资料收齐日期
                row1.CreateCell(22).SetCellValue(GetDateShowType(dr["OSubmitDate"]));//提交审核日期
                row1.CreateCell(23).SetCellValue(GetDateShowType(dr["PassDate"]));//审核通过日期
                row1.CreateCell(24).SetCellValue(GetDateShowType(dr["LoansDate"]));//放款日期
                row1.CreateCell(25).SetCellValue(dr["OContractPost"].ToString());//合同邮寄情况
                //row1.CreateCell(33).SetCellValue(dr["PassDay"].ToString());//审核时长(天)
                 
                //row1.CreateCell(35).SetCellValue(dr["LoansDay"].ToString());//放款时长(天)

                row1.CreateCell(26).SetCellValue(dr["CustomerMobile"].ToString());//联系电话
                row1.CreateCell(27).SetCellValue(dr["CustomerAddress"].ToString());//地址
                row1.CreateCell(28).SetCellValue(dr["GPSNumber"].ToString());//GPS号
                //row1.CreateCell(39).SetCellValue(getGPSStatus(dr["GPSStatus"]));//GPS状态
                //row1.CreateCell(40).SetCellValue(getCleanUpStatus(dr["CleanUpStatus"]));//结清状态
                row1.CreateCell(29).SetCellValue(getOInsureStatus(dr["OInsureStatus"]));//投保情况
                row1.CreateCell(30).SetCellValue(dr["ORemark"].ToString());//备注

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            //合计行
            //HSSFRow rowSum = (HSSFRow)sheet1.CreateRow(rowReal);
            //rowSum.CreateCell(0).SetCellValue("合计:");
            //rowSum.CreateCell(13).SetCellFormula("sum(N" + (rowindexBase + 1).ToString() + ":N" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(15).SetCellFormula("sum(P" + (rowindexBase + 1).ToString() + ":P" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(16).SetCellFormula("sum(Q" + (rowindexBase + 1).ToString() + ":Q" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(19).SetCellFormula("sum(T" + (rowindexBase + 1).ToString() + ":T" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(20).SetCellFormula("sum(U" + (rowindexBase + 1).ToString() + ":U" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(21).SetCellFormula("sum(V" + (rowindexBase + 1).ToString() + ":V" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(22).SetCellFormula("sum(W" + (rowindexBase + 1).ToString() + ":W" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(23).SetCellFormula("sum(X" + (rowindexBase + 1).ToString() + ":X" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(24).SetCellFormula("sum(Y" + (rowindexBase + 1).ToString() + ":Y" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(26).SetCellFormula("sum(AA" + (rowindexBase + 1).ToString() + ":AA" + (rowReal).ToString() + ")");

            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            WriteToFile();
        }
        /// <summary>
        ///IHISCE审核中
        /// </summary>
        /// <param name="exportDT"></param>
        private void export0(DataTable exportDT)
        {
            //DataTable exportDT = dts[0];

            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //写标题
            HSSFRow rowTitle = (HSSFRow)sheet1.CreateRow(0);
            string DTTitle = "融资租赁进度表(IHISCE审核中)";

            ICell cell = rowTitle.CreateCell(0);
            cell.SetCellValue(DTTitle);
            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeight = 20 * 20;
            style.SetFont(font);
            cell.CellStyle = style;

            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 29));

            //FOR写入
            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {


                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["FnncLsId"].ToString());//编号
                // row1.CreateCell(1).SetCellValue(dr["ContractNum"].ToString());//合同号
                row1.CreateCell(1).SetCellValue(dr["LeaseCompany"].ToString());//租赁公司
                row1.CreateCell(2).SetCellValue(dr["AliasName"].ToString());//经销商
                row1.CreateCell(3).SetCellValue(dr["CustomerName"].ToString());//用户
                row1.CreateCell(4).SetCellValue(dr["MachineType"].ToString());//机型
                row1.CreateCell(5).SetCellValue(dr["SerialNumber"].ToString());//机号
                //row1.CreateCell(7).SetCellValue(dr["LTotalPeriods"].ToString());//总期数
                //row1.CreateCell(8).SetCellValue(dr["LEvadePeriods"].ToString());//应付期数
                //row1.CreateCell(9).SetCellValue(dr["LPaidPeriods"].ToString());//已付期数
                //row1.CreateCell(10).SetCellValue(dr["LExpiryPeriods"].ToString());//逾期期数
                //row1.CreateCell(11).SetCellValue(dr["LastPayDate"].ToString());//最后支付时间
                row1.CreateCell(6).SetCellValue(dr["LExpiresYear"].ToString());//期限(年)
                row1.CreateCell(7).SetCellValue(GetDBDouble(dr["LTotalPrice"]));//售价
                row1.CreateCell(8).SetCellValue(dr["LowProportion"].ToString());//首付(%)
                row1.CreateCell(9).SetCellValue(GetDBDouble(dr["LDownAmount"]));//首付金额
                row1.CreateCell(10).SetCellValue(GetDBDouble(dr["LAmountFinancing"]));//融资额
                string LMonthRentStr = "";
                LMonthRentStr = dr["LMonthRent"].ToString();
                LMonthRentStr += Convert.ToDecimal(dr["LMonthRent2"]) == 0 ? "" : ("/" + dr["LMonthRent2"].ToString());
                row1.CreateCell(11).SetCellValue(LMonthRentStr);//月租金
                string LExpiresMonthStr = "";
                LExpiresMonthStr = dr["LExpiresMonth"].ToString();
                LExpiresMonthStr += Convert.ToDecimal(dr["LExpiresMonth2"]) == 0 ? "" : ("/" + dr["LExpiresMonth2"].ToString());
                row1.CreateCell(12).SetCellValue(LExpiresMonthStr);//期数
                //row1.CreateCell(19).SetCellValue(GetDBDouble(dr["LEvadeRent"]));//总应付租金
                //row1.CreateCell(20).SetCellValue(GetDBDouble(dr["LPaidRent"]));//累计已付租金
                //row1.CreateCell(21).SetCellValue(GetDBDouble(dr["LResiduerRent"]));//剩余租金
                row1.CreateCell(13).SetCellValue(GetDBDouble(dr["LContractAmount"]));//合同金额
                row1.CreateCell(14).SetCellValue(GetDBDouble(dr["LByStages"]));//首付(置换)分期申请
                row1.CreateCell(15).SetCellValue(GetDBDouble(dr["LPay"]));//应付首付款
                row1.CreateCell(16).SetCellValue(getLPayStatus(dr["LPayStatus"]));//应付首付款支付
                row1.CreateCell(17).SetCellValue(GetDBDouble(dr["LMFee"]));//管理费
                row1.CreateCell(18).SetCellValue(getLMFeeStatus(dr["LMFeeStatus"]));//管理费支付
                //row1.CreateCell(28).SetCellValue(getMaterialStatus(dr["MaterialStatus"]));//资料、协议收集完
                row1.CreateCell(19).SetCellValue(GetDateShowType(dr["LOrderDate"]));//接单日期
                row1.CreateCell(20).SetCellValue(GetDateShowType(dr["OGetDate"]));//资料收齐日期
                row1.CreateCell(21).SetCellValue(GetDateShowType(dr["OSubmitDate"]));//提交审核日期
                //row1.CreateCell(22).SetCellValue(GetDateShowType(dr["PassDate"]));//审核通过日期
                row1.CreateCell(22).SetCellValue(dr["OContractPost"].ToString());//合同邮寄情况
                //row1.CreateCell(33).SetCellValue(dr["PassDay"].ToString());//审核时长(天)
                // row1.CreateCell(34).SetCellValue(GetDateShowType(dr["LoansDate"]));//放款日期
                //row1.CreateCell(35).SetCellValue(dr["LoansDay"].ToString());//放款时长(天)

                row1.CreateCell(23).SetCellValue(dr["CustomerMobile"].ToString());//联系电话
                row1.CreateCell(24).SetCellValue(dr["CustomerAddress"].ToString());//地址
                row1.CreateCell(25).SetCellValue(dr["GPSNumber"].ToString());//GPS号
                //row1.CreateCell(39).SetCellValue(getGPSStatus(dr["GPSStatus"]));//GPS状态
                //row1.CreateCell(40).SetCellValue(getCleanUpStatus(dr["CleanUpStatus"]));//结清状态
                row1.CreateCell(26).SetCellValue(getOInsureStatus(dr["OInsureStatus"]));//投保情况
                row1.CreateCell(27).SetCellValue(dr["ORemark"].ToString());//备注

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            //合计行
            //HSSFRow rowSum = (HSSFRow)sheet1.CreateRow(rowReal);
            //rowSum.CreateCell(0).SetCellValue("合计:");
            //rowSum.CreateCell(13).SetCellFormula("sum(N" + (rowindexBase + 1).ToString() + ":N" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(15).SetCellFormula("sum(P" + (rowindexBase + 1).ToString() + ":P" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(16).SetCellFormula("sum(Q" + (rowindexBase + 1).ToString() + ":Q" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(19).SetCellFormula("sum(T" + (rowindexBase + 1).ToString() + ":T" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(20).SetCellFormula("sum(U" + (rowindexBase + 1).ToString() + ":U" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(21).SetCellFormula("sum(V" + (rowindexBase + 1).ToString() + ":V" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(22).SetCellFormula("sum(W" + (rowindexBase + 1).ToString() + ":W" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(23).SetCellFormula("sum(X" + (rowindexBase + 1).ToString() + ":X" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(24).SetCellFormula("sum(Y" + (rowindexBase + 1).ToString() + ":Y" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(26).SetCellFormula("sum(AA" + (rowindexBase + 1).ToString() + ":AA" + (rowReal).ToString() + ")");

            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            WriteToFile();
        }
        /// <summary>
        ///租赁公司审核中
        /// </summary>
        /// <param name="exportDT"></param>
        private void export1(DataTable exportDT)
        {
            //DataTable exportDT = dts[0];

            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //写标题
            HSSFRow rowTitle = (HSSFRow)sheet1.CreateRow(0);
            string DTTitle = "融资租赁进度表(租赁公司审核中)";

            ICell cell = rowTitle.CreateCell(0);
            cell.SetCellValue(DTTitle);
            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeight = 20 * 20;
            style.SetFont(font);
            cell.CellStyle = style;

            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 29));

            //FOR写入
            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {


                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["FnncLsId"].ToString());//编号
                // row1.CreateCell(1).SetCellValue(dr["ContractNum"].ToString());//合同号
                row1.CreateCell(1).SetCellValue(dr["LeaseCompany"].ToString());//租赁公司
                row1.CreateCell(2).SetCellValue(dr["AliasName"].ToString());//经销商
                row1.CreateCell(3).SetCellValue(dr["CustomerName"].ToString());//用户
                row1.CreateCell(4).SetCellValue(dr["MachineType"].ToString());//机型
                row1.CreateCell(5).SetCellValue(dr["SerialNumber"].ToString());//机号
                //row1.CreateCell(7).SetCellValue(dr["LTotalPeriods"].ToString());//总期数
                //row1.CreateCell(8).SetCellValue(dr["LEvadePeriods"].ToString());//应付期数
                //row1.CreateCell(9).SetCellValue(dr["LPaidPeriods"].ToString());//已付期数
                //row1.CreateCell(10).SetCellValue(dr["LExpiryPeriods"].ToString());//逾期期数
                //row1.CreateCell(11).SetCellValue(dr["LastPayDate"].ToString());//最后支付时间
                row1.CreateCell(6).SetCellValue(dr["LExpiresYear"].ToString());//期限(年)
                row1.CreateCell(7).SetCellValue(GetDBDouble(dr["LTotalPrice"]));//售价
                row1.CreateCell(8).SetCellValue(dr["LowProportion"].ToString());//首付(%)
                row1.CreateCell(9).SetCellValue(GetDBDouble(dr["LDownAmount"]));//首付金额
                row1.CreateCell(10).SetCellValue(GetDBDouble(dr["LAmountFinancing"]));//融资额
                string LMonthRentStr = "";
                LMonthRentStr = dr["LMonthRent"].ToString();
                LMonthRentStr += Convert.ToDecimal(dr["LMonthRent2"]) == 0 ? "" : ("/" + dr["LMonthRent2"].ToString());
                row1.CreateCell(11).SetCellValue(LMonthRentStr);//月租金
                string LExpiresMonthStr = "";
                LExpiresMonthStr = dr["LExpiresMonth"].ToString();
                LExpiresMonthStr += Convert.ToDecimal(dr["LExpiresMonth2"]) == 0 ? "" : ("/" + dr["LExpiresMonth2"].ToString());
                row1.CreateCell(12).SetCellValue(LExpiresMonthStr);//期数
                //row1.CreateCell(19).SetCellValue(GetDBDouble(dr["LEvadeRent"]));//总应付租金
                //row1.CreateCell(20).SetCellValue(GetDBDouble(dr["LPaidRent"]));//累计已付租金
                //row1.CreateCell(21).SetCellValue(GetDBDouble(dr["LResiduerRent"]));//剩余租金
                row1.CreateCell(13).SetCellValue(GetDBDouble(dr["LContractAmount"]));//合同金额
                row1.CreateCell(14).SetCellValue(GetDBDouble(dr["LByStages"]));//首付(置换)分期申请
                row1.CreateCell(15).SetCellValue(GetDBDouble(dr["LPay"]));//应付首付款
                row1.CreateCell(16).SetCellValue(getLPayStatus(dr["LPayStatus"]));//应付首付款支付
                row1.CreateCell(17).SetCellValue(GetDBDouble(dr["LMFee"]));//管理费
                row1.CreateCell(18).SetCellValue(getLMFeeStatus(dr["LMFeeStatus"]));//管理费支付
                //row1.CreateCell(28).SetCellValue(getMaterialStatus(dr["MaterialStatus"]));//资料、协议收集完
                row1.CreateCell(19).SetCellValue(GetDateShowType(dr["LOrderDate"]));//接单日期
                row1.CreateCell(20).SetCellValue(GetDateShowType(dr["OGetDate"]));//资料收齐日期
                row1.CreateCell(21).SetCellValue(GetDateShowType(dr["OSubmitDate"]));//提交审核日期
                //row1.CreateCell(22).SetCellValue(GetDateShowType(dr["PassDate"]));//审核通过日期
                row1.CreateCell(22).SetCellValue(dr["OContractPost"].ToString());//合同邮寄情况
                //row1.CreateCell(33).SetCellValue(dr["PassDay"].ToString());//审核时长(天)
                // row1.CreateCell(34).SetCellValue(GetDateShowType(dr["LoansDate"]));//放款日期
                //row1.CreateCell(35).SetCellValue(dr["LoansDay"].ToString());//放款时长(天)

                row1.CreateCell(23).SetCellValue(dr["CustomerMobile"].ToString());//联系电话
                row1.CreateCell(24).SetCellValue(dr["CustomerAddress"].ToString());//地址
                row1.CreateCell(25).SetCellValue(dr["GPSNumber"].ToString());//GPS号
                //row1.CreateCell(39).SetCellValue(getGPSStatus(dr["GPSStatus"]));//GPS状态
                //row1.CreateCell(40).SetCellValue(getCleanUpStatus(dr["CleanUpStatus"]));//结清状态
                row1.CreateCell(26).SetCellValue(getOInsureStatus(dr["OInsureStatus"]));//投保情况
                row1.CreateCell(27).SetCellValue(dr["ORemark"].ToString());//备注

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            //合计行
            //HSSFRow rowSum = (HSSFRow)sheet1.CreateRow(rowReal);
            //rowSum.CreateCell(0).SetCellValue("合计:");
            //rowSum.CreateCell(13).SetCellFormula("sum(N" + (rowindexBase + 1).ToString() + ":N" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(15).SetCellFormula("sum(P" + (rowindexBase + 1).ToString() + ":P" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(16).SetCellFormula("sum(Q" + (rowindexBase + 1).ToString() + ":Q" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(19).SetCellFormula("sum(T" + (rowindexBase + 1).ToString() + ":T" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(20).SetCellFormula("sum(U" + (rowindexBase + 1).ToString() + ":U" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(21).SetCellFormula("sum(V" + (rowindexBase + 1).ToString() + ":V" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(22).SetCellFormula("sum(W" + (rowindexBase + 1).ToString() + ":W" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(23).SetCellFormula("sum(X" + (rowindexBase + 1).ToString() + ":X" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(24).SetCellFormula("sum(Y" + (rowindexBase + 1).ToString() + ":Y" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(26).SetCellFormula("sum(AA" + (rowindexBase + 1).ToString() + ":AA" + (rowReal).ToString() + ")");

            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            WriteToFile();
        }
        /// <summary>
        ///审核通过
        /// </summary>
        /// <param name="exportDT"></param>
        private void export2(DataTable exportDT)
        {
            //DataTable exportDT = dts[0];

            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //写标题
            HSSFRow rowTitle = (HSSFRow)sheet1.CreateRow(0);
            string DTTitle = "融资租赁进度表(审核通过)";

            ICell cell = rowTitle.CreateCell(0);
            cell.SetCellValue(DTTitle);
            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeight = 20 * 20;
            style.SetFont(font);
            cell.CellStyle = style;

            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 29));

            //FOR写入
            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {


                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["FnncLsId"].ToString());//编号
               // row1.CreateCell(1).SetCellValue(dr["ContractNum"].ToString());//合同号
                row1.CreateCell(1).SetCellValue(dr["LeaseCompany"].ToString());//租赁公司
                row1.CreateCell(2).SetCellValue(dr["AliasName"].ToString());//经销商
                row1.CreateCell(3).SetCellValue(dr["CustomerName"].ToString());//用户
                row1.CreateCell(4).SetCellValue(dr["MachineType"].ToString());//机型
                row1.CreateCell(5).SetCellValue(dr["SerialNumber"].ToString());//机号
                //row1.CreateCell(7).SetCellValue(dr["LTotalPeriods"].ToString());//总期数
                //row1.CreateCell(8).SetCellValue(dr["LEvadePeriods"].ToString());//应付期数
                //row1.CreateCell(9).SetCellValue(dr["LPaidPeriods"].ToString());//已付期数
                //row1.CreateCell(10).SetCellValue(dr["LExpiryPeriods"].ToString());//逾期期数
                //row1.CreateCell(11).SetCellValue(dr["LastPayDate"].ToString());//最后支付时间
                row1.CreateCell(6).SetCellValue(dr["LExpiresYear"].ToString());//期限(年)
                row1.CreateCell(7).SetCellValue(GetDBDouble(dr["LTotalPrice"]));//售价
                row1.CreateCell(8).SetCellValue(dr["LowProportion"].ToString());//首付(%)
                row1.CreateCell(9).SetCellValue(GetDBDouble(dr["LDownAmount"]));//首付金额
                row1.CreateCell(10).SetCellValue(GetDBDouble(dr["LAmountFinancing"]));//融资额
                string LMonthRentStr = "";
                LMonthRentStr = dr["LMonthRent"].ToString();
                LMonthRentStr += Convert.ToDecimal(dr["LMonthRent2"]) == 0 ? "" : ("/" + dr["LMonthRent2"].ToString());
                row1.CreateCell(11).SetCellValue(LMonthRentStr);//月租金
                string LExpiresMonthStr = "";
                LExpiresMonthStr = dr["LExpiresMonth"].ToString();
                LExpiresMonthStr += Convert.ToDecimal(dr["LExpiresMonth2"]) == 0 ? "" : ("/" + dr["LExpiresMonth2"].ToString());
                row1.CreateCell(12).SetCellValue(LExpiresMonthStr);//期数
                //row1.CreateCell(19).SetCellValue(GetDBDouble(dr["LEvadeRent"]));//总应付租金
                //row1.CreateCell(20).SetCellValue(GetDBDouble(dr["LPaidRent"]));//累计已付租金
                //row1.CreateCell(21).SetCellValue(GetDBDouble(dr["LResiduerRent"]));//剩余租金
                row1.CreateCell(13).SetCellValue(GetDBDouble(dr["LContractAmount"]));//合同金额
                row1.CreateCell(14).SetCellValue(GetDBDouble(dr["LByStages"]));//首付(置换)分期申请
                row1.CreateCell(15).SetCellValue(GetDBDouble(dr["LPay"]));//应付首付款
                row1.CreateCell(16).SetCellValue(getLPayStatus(dr["LPayStatus"]));//应付首付款支付
                row1.CreateCell(17).SetCellValue(GetDBDouble(dr["LMFee"]));//管理费
                row1.CreateCell(18).SetCellValue(getLMFeeStatus(dr["LMFeeStatus"]));//管理费支付
                //row1.CreateCell(28).SetCellValue(getMaterialStatus(dr["MaterialStatus"]));//资料、协议收集完
                row1.CreateCell(19).SetCellValue(GetDateShowType(dr["LOrderDate"]));//接单日期
                row1.CreateCell(20).SetCellValue(GetDateShowType(dr["OGetDate"]));//资料收齐日期
                row1.CreateCell(21).SetCellValue(GetDateShowType(dr["OSubmitDate"]));//提交审核日期
                row1.CreateCell(22).SetCellValue(GetDateShowType(dr["PassDate"]));//审核通过日期
                row1.CreateCell(23).SetCellValue(dr["OContractPost"].ToString());//合同邮寄情况
                //row1.CreateCell(33).SetCellValue(dr["PassDay"].ToString());//审核时长(天)
                // row1.CreateCell(34).SetCellValue(GetDateShowType(dr["LoansDate"]));//放款日期
                //row1.CreateCell(35).SetCellValue(dr["LoansDay"].ToString());//放款时长(天)

                row1.CreateCell(24).SetCellValue(dr["CustomerMobile"].ToString());//联系电话
                row1.CreateCell(25).SetCellValue(dr["CustomerAddress"].ToString());//地址
                row1.CreateCell(26).SetCellValue(dr["GPSNumber"].ToString());//GPS号
                //row1.CreateCell(39).SetCellValue(getGPSStatus(dr["GPSStatus"]));//GPS状态
                //row1.CreateCell(40).SetCellValue(getCleanUpStatus(dr["CleanUpStatus"]));//结清状态
                row1.CreateCell(27).SetCellValue(getOInsureStatus(dr["OInsureStatus"]));//投保情况
                row1.CreateCell(28).SetCellValue(dr["ORemark"].ToString());//备注

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            //合计行
            //HSSFRow rowSum = (HSSFRow)sheet1.CreateRow(rowReal);
            //rowSum.CreateCell(0).SetCellValue("合计:");
            //rowSum.CreateCell(13).SetCellFormula("sum(N" + (rowindexBase + 1).ToString() + ":N" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(15).SetCellFormula("sum(P" + (rowindexBase + 1).ToString() + ":P" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(16).SetCellFormula("sum(Q" + (rowindexBase + 1).ToString() + ":Q" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(19).SetCellFormula("sum(T" + (rowindexBase + 1).ToString() + ":T" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(20).SetCellFormula("sum(U" + (rowindexBase + 1).ToString() + ":U" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(21).SetCellFormula("sum(V" + (rowindexBase + 1).ToString() + ":V" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(22).SetCellFormula("sum(W" + (rowindexBase + 1).ToString() + ":W" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(23).SetCellFormula("sum(X" + (rowindexBase + 1).ToString() + ":X" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(24).SetCellFormula("sum(Y" + (rowindexBase + 1).ToString() + ":Y" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(26).SetCellFormula("sum(AA" + (rowindexBase + 1).ToString() + ":AA" + (rowReal).ToString() + ")");

            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            WriteToFile();
        }
        /// <summary>
        /// 待放款
        /// </summary>
        /// <param name="exportDT"></param>
        private void export3(DataTable exportDT)
        {
            //DataTable exportDT = dts[0];

            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //写标题
            HSSFRow rowTitle = (HSSFRow)sheet1.CreateRow(0);
            string DTTitle = "融资租赁进度表(待放款)";

            ICell cell = rowTitle.CreateCell(0);
            cell.SetCellValue(DTTitle);
            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeight = 20 * 20;
            style.SetFont(font);
            cell.CellStyle = style;

            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 43));

            //FOR写入
            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {


                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["FnncLsId"].ToString());//编号
                row1.CreateCell(1).SetCellValue(dr["ContractNum"].ToString());//合同号
                row1.CreateCell(2).SetCellValue(dr["LeaseCompany"].ToString());//租赁公司
                row1.CreateCell(3).SetCellValue(dr["AliasName"].ToString());//经销商
                row1.CreateCell(4).SetCellValue(dr["CustomerName"].ToString());//用户
                row1.CreateCell(5).SetCellValue(dr["MachineType"].ToString());//机型
                row1.CreateCell(6).SetCellValue(dr["SerialNumber"].ToString());//机号
                //row1.CreateCell(7).SetCellValue(dr["LTotalPeriods"].ToString());//总期数
                //row1.CreateCell(8).SetCellValue(dr["LEvadePeriods"].ToString());//应付期数
                //row1.CreateCell(9).SetCellValue(dr["LPaidPeriods"].ToString());//已付期数
                //row1.CreateCell(10).SetCellValue(dr["LExpiryPeriods"].ToString());//逾期期数
                //row1.CreateCell(11).SetCellValue(dr["LastPayDate"].ToString());//最后支付时间
                row1.CreateCell(7).SetCellValue(dr["LExpiresYear"].ToString());//期限(年)
                row1.CreateCell(8).SetCellValue(GetDBDouble(dr["LTotalPrice"]));//售价
                row1.CreateCell(9).SetCellValue(dr["LowProportion"].ToString());//首付(%)
                row1.CreateCell(10).SetCellValue(GetDBDouble(dr["LDownAmount"]));//首付金额
                row1.CreateCell(11).SetCellValue(GetDBDouble(dr["LAmountFinancing"]));//融资额
                string LMonthRentStr = "";
                LMonthRentStr = dr["LMonthRent"].ToString();
                LMonthRentStr += Convert.ToDecimal(dr["LMonthRent2"]) == 0 ? "" : ("/" + dr["LMonthRent2"].ToString());
                row1.CreateCell(12).SetCellValue(LMonthRentStr);//月租金
                string LExpiresMonthStr = "";
                LExpiresMonthStr = dr["LExpiresMonth"].ToString();
                LExpiresMonthStr += Convert.ToDecimal(dr["LExpiresMonth2"]) == 0 ? "" : ("/" + dr["LExpiresMonth2"].ToString());
                row1.CreateCell(13).SetCellValue(LExpiresMonthStr);//期数
                //row1.CreateCell(19).SetCellValue(GetDBDouble(dr["LEvadeRent"]));//总应付租金
                //row1.CreateCell(20).SetCellValue(GetDBDouble(dr["LPaidRent"]));//累计已付租金
                //row1.CreateCell(21).SetCellValue(GetDBDouble(dr["LResiduerRent"]));//剩余租金
                row1.CreateCell(14).SetCellValue(GetDBDouble(dr["LContractAmount"]));//合同金额
                row1.CreateCell(15).SetCellValue(GetDBDouble(dr["LByStages"]));//首付(置换)分期申请
                row1.CreateCell(16).SetCellValue(GetDBDouble(dr["LPay"]));//应付首付款
                row1.CreateCell(17).SetCellValue(getLPayStatus(dr["LPayStatus"]));//应付首付款支付
                row1.CreateCell(18).SetCellValue(GetDBDouble(dr["LMFee"]));//管理费
                row1.CreateCell(19).SetCellValue(getLMFeeStatus(dr["LMFeeStatus"]));//管理费支付
                //row1.CreateCell(28).SetCellValue(getMaterialStatus(dr["MaterialStatus"]));//资料、协议收集完
                row1.CreateCell(20).SetCellValue(GetDateShowType(dr["LOrderDate"]));//接单日期
                row1.CreateCell(21).SetCellValue(GetDateShowType(dr["OGetDate"]));//资料收齐日期
                row1.CreateCell(22).SetCellValue(GetDateShowType(dr["OSubmitDate"]));//提交审核日期
                row1.CreateCell(23).SetCellValue(GetDateShowType(dr["PassDate"]));//审核通过日期
                //row1.CreateCell(33).SetCellValue(dr["PassDay"].ToString());//审核时长(天)
               // row1.CreateCell(34).SetCellValue(GetDateShowType(dr["LoansDate"]));//放款日期
                //row1.CreateCell(35).SetCellValue(dr["LoansDay"].ToString());//放款时长(天)
                row1.CreateCell(24).SetCellValue(dr["CustomerMobile"].ToString());//联系电话
                row1.CreateCell(25).SetCellValue(dr["CustomerAddress"].ToString());//地址
                row1.CreateCell(26).SetCellValue(dr["GPSNumber"].ToString());//GPS号
                //row1.CreateCell(39).SetCellValue(getGPSStatus(dr["GPSStatus"]));//GPS状态
                //row1.CreateCell(40).SetCellValue(getCleanUpStatus(dr["CleanUpStatus"]));//结清状态
                row1.CreateCell(27).SetCellValue(getOInsureStatus(dr["OInsureStatus"]));//投保情况
                row1.CreateCell(28).SetCellValue(dr["ORemark"].ToString());//备注

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            //合计行
            //HSSFRow rowSum = (HSSFRow)sheet1.CreateRow(rowReal);
            //rowSum.CreateCell(0).SetCellValue("合计:");
            //rowSum.CreateCell(13).SetCellFormula("sum(N" + (rowindexBase + 1).ToString() + ":N" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(15).SetCellFormula("sum(P" + (rowindexBase + 1).ToString() + ":P" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(16).SetCellFormula("sum(Q" + (rowindexBase + 1).ToString() + ":Q" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(19).SetCellFormula("sum(T" + (rowindexBase + 1).ToString() + ":T" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(20).SetCellFormula("sum(U" + (rowindexBase + 1).ToString() + ":U" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(21).SetCellFormula("sum(V" + (rowindexBase + 1).ToString() + ":V" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(22).SetCellFormula("sum(W" + (rowindexBase + 1).ToString() + ":W" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(23).SetCellFormula("sum(X" + (rowindexBase + 1).ToString() + ":X" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(24).SetCellFormula("sum(Y" + (rowindexBase + 1).ToString() + ":Y" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(26).SetCellFormula("sum(AA" + (rowindexBase + 1).ToString() + ":AA" + (rowReal).ToString() + ")");

            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            WriteToFile();
        }
        /// <summary>
        /// 已放款导出
        /// </summary>
        private void export4(DataTable exportDT)
        {
            //DataTable exportDT = dts[0];

            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //写标题
            HSSFRow rowTitle = (HSSFRow)sheet1.CreateRow(0);
            string DTTitle = "融资租赁进度表(已放款)";
           
            ICell cell = rowTitle.CreateCell(0);
            cell.SetCellValue(DTTitle);
            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.LEFT;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeight = 20 * 20;
            style.SetFont(font);
            cell.CellStyle = style;

            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 42));

            //FOR写入
            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {


                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["FnncLsId"].ToString());//编号
                row1.CreateCell(1).SetCellValue(dr["ContractNum"].ToString());//合同号
                row1.CreateCell(2).SetCellValue(dr["LeaseCompany"].ToString());//租赁公司
                row1.CreateCell(3).SetCellValue(dr["AliasName"].ToString());//经销商
                row1.CreateCell(4).SetCellValue(dr["CustomerName"].ToString());//用户
                row1.CreateCell(5).SetCellValue(dr["MachineType"].ToString());//机型
                row1.CreateCell(6).SetCellValue(dr["SerialNumber"].ToString());//机号
                row1.CreateCell(7).SetCellValue(dr["LTotalPeriods"].ToString());//总期数
                row1.CreateCell(8).SetCellValue(dr["LEvadePeriods"].ToString());//应付期数
                row1.CreateCell(9).SetCellValue(dr["LPaidPeriods"].ToString());//已付期数
                row1.CreateCell(10).SetCellValue(dr["LExpiryPeriods"].ToString());//逾期期数
                row1.CreateCell(11).SetCellValue(dr["LastPayDate"].ToString());//最后支付时间
                row1.CreateCell(12).SetCellValue(dr["LExpiresYear"].ToString());//期限(年)
                row1.CreateCell(13).SetCellValue(GetDBDouble(dr["LTotalPrice"]));//售价
                row1.CreateCell(14).SetCellValue(dr["LowProportion"].ToString());//首付(%)
                row1.CreateCell(15).SetCellValue(GetDBDouble(dr["LDownAmount"]));//首付金额
                row1.CreateCell(16).SetCellValue(GetDBDouble(dr["LAmountFinancing"]));//融资额
                string LMonthRentStr = "";
                LMonthRentStr = dr["LMonthRent"].ToString();
                LMonthRentStr += Convert.ToDecimal(dr["LMonthRent2"]) == 0 ? "" :( "/" + dr["LMonthRent2"].ToString());
                row1.CreateCell(17).SetCellValue(LMonthRentStr);//月租金
                string LExpiresMonthStr = "";
                LExpiresMonthStr = dr["LExpiresMonth"].ToString();
                LExpiresMonthStr += Convert.ToDecimal(dr["LExpiresMonth2"]) == 0 ? "" : ("/" + dr["LExpiresMonth2"].ToString());
                row1.CreateCell(18).SetCellValue(LExpiresMonthStr);//期数
                row1.CreateCell(19).SetCellValue(GetDBDouble(dr["LEvadeRent"]));//总应付租金
                row1.CreateCell(20).SetCellValue(GetDBDouble(dr["LPaidRent"]));//累计已付租金
                row1.CreateCell(21).SetCellValue(GetDBDouble(dr["LResiduerRent"]));//剩余租金
                row1.CreateCell(22).SetCellValue(GetDBDouble(dr["LContractAmount"]));//合同金额
                row1.CreateCell(23).SetCellValue(GetDBDouble(dr["LByStages"]));//首付(置换)分期申请
                row1.CreateCell(24).SetCellValue(GetDBDouble(dr["LPay"]));//应付首付款
                row1.CreateCell(25).SetCellValue(getLPayStatus(dr["LPayStatus"]));//应付首付款支付
                row1.CreateCell(26).SetCellValue(GetDBDouble(dr["LMFee"]));//管理费
                row1.CreateCell(27).SetCellValue(getLMFeeStatus(dr["LMFeeStatus"]));//管理费支付
                row1.CreateCell(28).SetCellValue(getMaterialStatus(dr["MaterialStatus"]));//资料、协议收集完
                row1.CreateCell(29).SetCellValue(GetDateShowType(dr["LOrderDate"]));//接单日期
                row1.CreateCell(30).SetCellValue(GetDateShowType(dr["OGetDate"]));//资料收齐日期
                row1.CreateCell(31).SetCellValue(GetDateShowType(dr["OSubmitDate"]));//提交审核日期
                row1.CreateCell(32).SetCellValue(GetDateShowType(dr["PassDate"]));//审核通过日期
                row1.CreateCell(33).SetCellValue(dr["PassDay"].ToString());//审核时长(天)
                row1.CreateCell(34).SetCellValue(GetDateShowType(dr["LoansDate"]));//放款日期
                row1.CreateCell(35).SetCellValue(dr["LoansDay"].ToString());//放款时长(天)
                row1.CreateCell(36).SetCellValue(dr["CustomerMobile"].ToString());//联系电话
                row1.CreateCell(37).SetCellValue(dr["CustomerAddress"].ToString());//地址
                row1.CreateCell(38).SetCellValue(dr["GPSNumber"].ToString());//GPS号
                row1.CreateCell(39).SetCellValue(getGPSStatus(dr["GPSStatus"]));//GPS状态
                row1.CreateCell(40).SetCellValue(getCleanUpStatus(dr["CleanUpStatus"]));//结清状态
                row1.CreateCell(41).SetCellValue(getOInsureStatus(dr["OInsureStatus"]));//投保情况
                row1.CreateCell(42).SetCellValue(dr["ORemark"].ToString());//备注

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            //合计行
            HSSFRow rowSum = (HSSFRow)sheet1.CreateRow(rowReal);
            rowSum.CreateCell(0).SetCellValue("合计:");
            rowSum.CreateCell(13).SetCellFormula("sum(N" + (rowindexBase + 1).ToString() + ":N" + (rowReal).ToString() + ")");
            rowSum.CreateCell(15).SetCellFormula("sum(P" + (rowindexBase + 1).ToString() + ":P" + (rowReal).ToString() + ")");
            rowSum.CreateCell(16).SetCellFormula("sum(Q" + (rowindexBase + 1).ToString() + ":Q" + (rowReal).ToString() + ")");
            rowSum.CreateCell(19).SetCellFormula("sum(T" + (rowindexBase + 1).ToString() + ":T" + (rowReal).ToString() + ")");
            rowSum.CreateCell(20).SetCellFormula("sum(U" + (rowindexBase + 1).ToString() + ":U" + (rowReal).ToString() + ")");
            rowSum.CreateCell(21).SetCellFormula("sum(V" + (rowindexBase + 1).ToString() + ":V" + (rowReal).ToString() + ")");
            rowSum.CreateCell(22).SetCellFormula("sum(W" + (rowindexBase + 1).ToString() + ":W" + (rowReal).ToString() + ")");
            rowSum.CreateCell(23).SetCellFormula("sum(X" + (rowindexBase + 1).ToString() + ":X" + (rowReal).ToString() + ")");
            rowSum.CreateCell(24).SetCellFormula("sum(Y" + (rowindexBase + 1).ToString() + ":Y" + (rowReal).ToString() + ")");
            rowSum.CreateCell(26).SetCellFormula("sum(AA" + (rowindexBase + 1).ToString() + ":AA" + (rowReal).ToString() + ")");
         
            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            WriteToFile();
        }

        private string GetDBString(object v)
        {
            if (v is DBNull)
                return "";
            return v.ToString();
        }

        private Double GetDBDouble(object v)
        {
            if (v is DBNull)
                return 0;
            return Convert.ToDouble(v);
        }
    }
}